Hi All!
I have a MySQL table like this -
ID NAME PARENT
== ======== ===========
1 Class 0
2 Math 1
3 Physics 1
4 Cooking 0
5 Italian 4
6 Chinese 4
I want a query that will give me this output -
ID NAME
== ===================
1 Class
2 Math Class
3 Physics Class
4 Cooking
5 Italian Cooking
6 Chinese Cooking
The parent's name will be appended with the child's name, like surname.
Please help me!
Any succession will be appreciated!
AVADHESH PATEL
02-May-2013You can achieve above description as below using LEFT JOIN
LEFT JOIN is needed on this case since there are PARENT that has no match on the ID.
SELECT a.ID,
CONCAT(a.Name, ' ', COALESCE(b.name,'')) Name
FROM TableName a
LEFT JOIN TableName b
ON a.Parent = b.ID
SQLFiddle Demo
you can also use CONCAT_WS() so that you can omit COALESCE()
SELECT a.ID,
CONCAT_WS(' ', a.Name, b.name) Name
FROM TableName a
LEFT JOIN TableName b
ON a.Parent = b.ID